﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

using Lenovo.CFI.Common;

namespace Lenovo.CFI.DAL
{
    public class IdeaReviewDa
    {
        #region review

        /// <summary>
        /// 有问题
        /// </summary>
        /// <param name="topicID"></param>
        /// <param name="ideaID"></param>
        /// <param name="ideaStatus"></param>
        /// <param name="dcOwner"></param>
        /// <param name="ipOwner"></param>
        /// <param name="executor"></param>
        /// <returns></returns>
        public static List<IdeaReview> GetReview(int? topicID, int? ideaID, IdeaStatus? ideaStatus, string dcOwner, string ipOwner, string executor)
        {
            return GetReview(topicID, ideaID, ideaStatus, dcOwner, ipOwner, executor, DBHelper.GetDatabase(), null);
        }
        public static List<IdeaReview> GetReview(int? topicID, int? ideaID, IdeaStatus? ideaStatus, string dcOwner, string ipOwner, string executor, TranscationHelper trans)
        {
            return GetReview(topicID, ideaID, ideaStatus, dcOwner, ipOwner, executor, trans.DataBase, trans.Transaction);
        }
        private static List<IdeaReview> GetReview(int? topicID, int? ideaID, IdeaStatus? ideaStatus, string dcOwner, string ipOwner, string executor, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaReview", topicID, ideaID, ideaStatus, dcOwner, ipOwner, executor);

            List<IdeaReview> ideaReviews = new List<IdeaReview>();

            IDataReader dataReader;
            if (trans == null)
                dataReader = db.ExecuteReader(dbCommand);
            else
                dataReader = db.ExecuteReader(dbCommand, trans);

            using (dataReader)
            {
                while (dataReader.Read())
                {
                    ideaReviews.Add(PopulateReview(dataReader));
                }
            }

            return ideaReviews;
        }

        public static IdeaReview GetReviewByID(int id)
        {
            return GetReviewByID(id, DBHelper.GetDatabase(), null);
        }
        public static IdeaReview GetReviewByID(int id, TranscationHelper trans)
        {
            return GetReviewByID(id, trans.DataBase, trans.Transaction);
        }
        private static IdeaReview GetReviewByID(int id, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaReviewByID", id);

            IDataReader dataReader;
            if (trans == null)
                dataReader = db.ExecuteReader(dbCommand);
            else
                dataReader = db.ExecuteReader(dbCommand, trans);

            using (dataReader)
            {
                if (dataReader.Read())
                {
                    return PopulateReview(dataReader);
                }
            }

            return null;
        }


        private static IdeaReview PopulateReview(IDataReader dataReader)
        {
            IdeaReview ideaReview = new IdeaReview(dataReader.GetInt32(0), dataReader.GetInt32(1), dataReader.GetInt32(2));

            ideaReview.ReviewTime = dataReader.IsDBNull(3) ? (DateTime?)null : dataReader.GetDateTime(3);
            ideaReview.ReviewPeriod = dataReader.GetInt32(4);
            ideaReview.OwnerConfirmed = dataReader.IsDBNull(5) ? (bool?)null : dataReader.GetBoolean(5);
            ideaReview.OwnerConfirmComment = dataReader.IsDBNull(6) ? null : dataReader.GetString(6);
            ideaReview.ReviewRecord = dataReader.IsDBNull(7) ? null : dataReader.GetString(7);
            ideaReview.ReviewStatus = (IdeaReviewStatus)dataReader.GetInt32(8);
            ideaReview.ReviewSummary = dataReader.IsDBNull(9) ? null : dataReader.GetString(9);
            ideaReview.ReviewScore = dataReader.GetDecimal(10);
            ideaReview.ReviewType = dataReader.IsDBNull(11) ? null : dataReader.GetString(11);
            ideaReview.ReviewIP = dataReader.IsDBNull(12) ? (bool?)null : dataReader.GetBoolean(12);
            ideaReview.DcOwner = dataReader.IsDBNull(13) ? null : dataReader.GetString(13);
            ideaReview.DcConfirmed = dataReader.IsDBNull(14) ? (bool?)null : dataReader.GetBoolean(14);
            ideaReview.IPOwner = dataReader.IsDBNull(15) ? null : dataReader.GetString(15);
            ideaReview.IPConfirmed = dataReader.IsDBNull(16) ? (bool?)null : dataReader.GetBoolean(16);
            ideaReview.NextAction = dataReader.IsDBNull(17) ? null : dataReader.GetString(17);
            ideaReview.NextActionPrev = dataReader.IsDBNull(18) ? null : dataReader.GetString(18);
            ideaReview.NextActionBu = dataReader.IsDBNull(19) ? null : dataReader.GetString(19);
            ideaReview.NextIP = dataReader.IsDBNull(20) ? (bool?)null : dataReader.GetBoolean(20);
            ideaReview.Award = dataReader.IsDBNull(21) ? (int?)null : dataReader.GetInt32(21);
            ideaReview.ActionExecutor = dataReader.IsDBNull(22) ? null : dataReader.GetString(22);
            ideaReview.ActionStatus = dataReader.IsDBNull(23) ? (ExecutionStatus?)null : (ExecutionStatus)dataReader.GetInt32(23);
            ideaReview.ProjectName = dataReader.IsDBNull(24) ? null : dataReader.GetString(24);
            ideaReview.ProjectTime = dataReader.IsDBNull(25) ? (DateTime?)null : dataReader.GetDateTime(25);
            ideaReview.ActionComment = dataReader.IsDBNull(26) ? null : dataReader.GetString(26);
            ideaReview.IpExecutor = dataReader.IsDBNull(27) ? null : dataReader.GetString(27);
            ideaReview.IpStatus = dataReader.IsDBNull(28) ? (ExecutionStatus?)null : (ExecutionStatus)dataReader.GetInt32(28);
            ideaReview.IpNo = dataReader.IsDBNull(29) ? null : dataReader.GetString(29);
            ideaReview.IpComment = dataReader.IsDBNull(30) ? null : dataReader.GetString(30);

            return ideaReview;
        }


        public static void InsertReview(IdeaReview ideaReview)
        {
            InsertReview(ideaReview, DBHelper.GetDatabase(), null);
        }
        public static void InsertReview(IdeaReview ideaReview, TranscationHelper trans)
        {
            InsertReview(ideaReview, trans.DataBase, trans.Transaction);
        }
        private static void InsertReview(IdeaReview ideaReview, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertIdeaReview",
                ideaReview.TopicID, ideaReview.IdeaID, ideaReview.ReviewTime, ideaReview.ReviewPeriod,
                ideaReview.OwnerConfirmed, ideaReview.OwnerConfirmComment,
                ideaReview.ReviewRecord, (int?)ideaReview.ReviewStatus, ideaReview.ReviewSummary, ideaReview.ReviewScore,
                ideaReview.ReviewType, ideaReview.ReviewIP,
                ideaReview.DcOwner, ideaReview.DcConfirmed, ideaReview.IPOwner, ideaReview.IPConfirmed,
                ideaReview.NextAction, ideaReview.NextActionPrev, ideaReview.NextActionBu, ideaReview.NextIP, ideaReview.Award, 
                ideaReview.ActionExecutor, (int?)ideaReview.ActionStatus, ideaReview.ProjectName, ideaReview.ProjectTime,
                ideaReview.IpExecutor, (int?)ideaReview.IpStatus, ideaReview.IpNo, ideaReview.IpComment);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void UpdateReview(IdeaReview ideaReview)
        {
            UpdateReview(ideaReview, DBHelper.GetDatabase(), null);
        }
        public static void UpdateReview(IdeaReview ideaReview, TranscationHelper trans)
        {
            UpdateReview(ideaReview, trans.DataBase, trans.Transaction);
        }
        private static void UpdateReview(IdeaReview ideaReview, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_UpdateIdeaReview", ideaReview.ID,
                ideaReview.OwnerConfirmed, ideaReview.OwnerConfirmComment,
                ideaReview.ReviewRecord, (int?)ideaReview.ReviewStatus, ideaReview.ReviewSummary, ideaReview.ReviewScore,
                ideaReview.ReviewType, ideaReview.ReviewIP,
                ideaReview.DcOwner, ideaReview.DcConfirmed, ideaReview.IPOwner, ideaReview.IPConfirmed,
                ideaReview.NextAction, ideaReview.NextActionPrev, ideaReview.NextActionBu, ideaReview.NextIP, ideaReview.Award,
                ideaReview.ActionExecutor, (int?)ideaReview.ActionStatus, ideaReview.ProjectName, ideaReview.ProjectTime,
                ideaReview.IpExecutor, (int?)ideaReview.IpStatus, ideaReview.IpNo, ideaReview.IpComment);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        #endregion


        #region detail

        /// <summary>
        /// 多条件查询
        /// </summary>
        /// <param name="topicID"></param>
        /// <param name="reviewID"></param>
        /// <param name="reviewer"></param>
        /// <param name="agent"></param>
        /// <param name="finished"></param>
        /// <returns></returns>
        public static List<IdeaReviewDetail> GetReviewDetail(int? topicID, int? reviewID, string reviewer, string agent, bool? finished)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaReviewDetail", topicID, reviewID, reviewer, agent, finished);

            List<IdeaReviewDetail> reviewDetails = new List<IdeaReviewDetail>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    reviewDetails.Add(PopulateReviewDetail(dataReader));
                }
            }

            return reviewDetails;
        }


        public static IdeaReviewDetail GetDetailByID(int id)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaReviewDetailByID", id);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    return PopulateReviewDetail(dataReader);
                }
            }

            return null;
        }


        private static IdeaReviewDetail PopulateReviewDetail(IDataReader dataReader)
        {
            IdeaReviewDetail reviewDetail = new IdeaReviewDetail(dataReader.GetInt32(0), dataReader.GetInt32(1), dataReader.GetInt32(2));

            reviewDetail.Reviewer = dataReader.GetString(3);
            reviewDetail.Agent = dataReader.GetString(4);
            reviewDetail.Cv = dataReader.IsDBNull(5) ? (decimal?)null : dataReader.GetDecimal(5);
            reviewDetail.Bv = dataReader.IsDBNull(6) ? (decimal?)null : dataReader.GetDecimal(6);
            reviewDetail.Tv = dataReader.IsDBNull(7) ? (decimal?)null : dataReader.GetDecimal(7);
            reviewDetail.Type = dataReader.IsDBNull(8) ? null : dataReader.GetString(8);
            reviewDetail.TypeComment = dataReader.IsDBNull(9) ? null : dataReader.GetString(9);
            reviewDetail.Ip = dataReader.IsDBNull(10) ? (bool?)null : dataReader.GetBoolean(10);
            reviewDetail.IpComment = dataReader.IsDBNull(11) ? null : dataReader.GetString(11);
            reviewDetail.Suggestion = dataReader.IsDBNull(12) ? null : dataReader.GetString(12);
            reviewDetail.Note = dataReader.IsDBNull(13) ? null : dataReader.GetString(13);
            reviewDetail.Stamp = dataReader.GetDateTime(14);
            reviewDetail.Finished = dataReader.GetBoolean(15);

            return reviewDetail;
        }


        public static void InsertReviewDetail(IdeaReviewDetail reviewDetail)
        {
            InsertReviewDetail(reviewDetail, DBHelper.GetDatabase(), null);
        }
        public static void InsertReviewDetail(IdeaReviewDetail reviewDetail, TranscationHelper trans)
        {
            InsertReviewDetail(reviewDetail, trans.DataBase, trans.Transaction);
        }
        private static void InsertReviewDetail(IdeaReviewDetail reviewDetail, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertIdeaReviewDetail",
                reviewDetail.ReviewID, reviewDetail.Reviewer, reviewDetail.Agent,
                reviewDetail.Cv, reviewDetail.Bv, reviewDetail.Tv,
                reviewDetail.Type, reviewDetail.TypeComment,
                reviewDetail.Ip, reviewDetail.IpComment,
                reviewDetail.Suggestion, reviewDetail.Note, reviewDetail.Stamp, reviewDetail.Finished);

            try
            {
                if (trans == null)
                    reviewDetail.ID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
                else
                    reviewDetail.ID = Convert.ToInt32(db.ExecuteScalar(dbCommand, trans));
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void UpdateReviewDetail(IdeaReviewDetail reviewDetail)
        {
            UpdateReviewDetail(reviewDetail, DBHelper.GetDatabase(), null);
        }
        public static void UpdateReviewDetail(IdeaReviewDetail reviewDetail, TranscationHelper trans)
        {
            UpdateReviewDetail(reviewDetail, trans.DataBase, trans.Transaction);
        }
        private static void UpdateReviewDetail(IdeaReviewDetail reviewDetail, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_UpdateIdeaReviewDetail", reviewDetail.ID, reviewDetail.Agent,
                reviewDetail.Cv, reviewDetail.Bv, reviewDetail.Tv,
                reviewDetail.Type, reviewDetail.TypeComment,
                reviewDetail.Ip, reviewDetail.IpComment,
                reviewDetail.Suggestion, reviewDetail.Note, reviewDetail.Stamp, reviewDetail.Finished);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        #endregion
    }
}
